package ZZ_backup;

import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;
import java.io.*;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;

import LawsonTransformJava.pgm.PR560RecType3ACD;

import LawsonTransformJava.xref.XrefDepartment;
import LawsonTransformJava.xref.XrefProcessLevel;

import LawsonTransformJava.dao.DAOFactoryOracleDB;
import LawsonTransformJava.dao.DAOPayMastr;
import LawsonTransformJava.dao.DAObject;
import LawsonTransformJava.data.TblPayMastr;

public class PR560RecType3_backup extends DAObject implements 
    DAOPayMastr, Runnable {

	private XrefProcessLevel xProcessLevels = null;
	private XrefDepartment xDepartments = null;
	private int payrollYear = 0;
	
    public PR560RecType3 (File output, int payrollYear, boolean allowBlanks) throws SQLException {
    // initialization 
		try {
			//Append data to FileWriter = true
			this.output = new FileWriter(this.outputFile = output, true);
			this.payrollYear=payrollYear;
			init(allowBlanks);
		} catch (IOException e) {
			// Could no write properties file
			System.out.println("IOException: " + e);
		}
  }

  private void init(boolean allowBlanks) throws IOException, SQLException {
	    // Set the data variables
	    data = new DAOFactoryOracleDB();
	    xProcessLevels = new XrefProcessLevel();
	    xDepartments = new XrefDepartment(allowBlanks);
	    
//	    sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME;
	    stmtData = data.getDataStatement();	    
	    countStmtData = data.getCountStatement();
	    //Set the Date Format for output
	    df = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");
  }
  
  public TblPayMastr findPayMastr() {
	  TblPayMastr pym = new TblPayMastr();
		try {
			if (whereClause==null || whereClause.equals("")) {
				whereClause = " WHERE to_char(PER_END_DATE, \"yyyy\")=\"" + payrollYear + "\"";
			} else {
				whereClause = whereClause.trim() + " AND to_char(PER_END_DATE, 'yyyy')='" + payrollYear + "'";
			}
			sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME + " " + (whereClause==null?"":whereClause);
			rslt = data.getDataStatement().executeQuery(sqlData);
			rsmd = rslt.getMetaData();
			if (rslt.next()){
				hasMoreRecs = true;
				pym = (TblPayMastr) createObject(rslt, pym);
				pym.setEmpAchDist((new PR560RecType3ACD()).findEmployee(pym.getCompany(), Integer.toString(pym.getEmployee()), pym.getCheck_id()));
			}
			else
				hasMoreRecs = false;
			return pym;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

  public TblPayMastr findPayMastr(String employeeNumber) {
	try {
		TblPayMastr pym = new TblPayMastr();
		
		if (whereClause==null) {
			whereClause = " WHERE EMPLOYEE=" + employeeNumber + " AND to_char(PER_END_DATE, 'yyyy')='" + payrollYear + "'";
		} else {
			whereClause = whereClause.trim() + " AND EMPLOYEE=" + employeeNumber + " AND to_char(PER_END_DATE, \"yyyy\")=\"" + payrollYear + "\"";
		}
		sqlData = "SELECT * FROM " + data.getSchema() + "." + TABLE_NAME + " " + (whereClause==null?"":whereClause);
		rslt = data.getDataStatement().executeQuery(sqlData);
		rsmd = rslt.getMetaData();
		rslt.next();
		pym = (TblPayMastr) createObject(rslt, pym);
		pym.setEmpAchDist((new PR560RecType3ACD()).findEmployee(pym.getCompany(), Integer.toString(pym.getEmployee()), pym.getCheck_id()));
		return pym;
	} catch (SQLException e) {
	    // Could not connect to the database
		System.out.println("SQLException: " + e);
	} catch (InvocationTargetException ite) {
		System.out.println("InvocationException: " + ite);
	} catch (IllegalAccessException iae) {
		System.out.println("IllegalAccessException: " + iae);
	}
	return new TblPayMastr();
  }

  public TblPayMastr nextPayMastr() {
	  TblPayMastr pym = new TblPayMastr();
		try {
			if (rslt.next()){
				hasMoreRecs = true;
				pym = (TblPayMastr) createObject(rslt, pym);
				pym.setEmpAchDist((new PR560RecType3ACD()).findEmployee(pym.getCompany(), Integer.toString(pym.getEmployee()), pym.getCheck_id()));
			}
			else
				hasMoreRecs = false;
			return pym;
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		} catch (InvocationTargetException ite) {
			System.out.println("InvocationException: " + ite);
		} catch (IllegalAccessException iae) {
			System.out.println("IllegalAccessException: " + iae);
		}
	  return null;
}

	public boolean hasMore () {
	  return hasMoreRecs;
	}

  public int countPayMastr() {
	try {
		countRecs = data.getCountStatement().executeQuery("SELECT COUNT(*) FROM " + data.getSchema() + "." + TABLE_NAME + (whereClause==null?"":whereClause));
		countRecs.next();
		return recCount=countRecs.getInt(1);
		} catch (SQLException e) {
		    // Could not connect to the database
			System.out.println("SQLException: " + e);
		}
		return 0;
  }
  
  public void run() {
	  TblPayMastr pym = null;
	  
	  try {
		//Update current date/time
		Date now = new Date();
		System.out.println(outputFile.getName() + " Start " + df.format(now));
		//Write timestamp to output file
//		output.write(outputFile.getName() + " Start " + df.format(now) + "\n");
		
		//@ TODO DEBUG BEGIN - remove lines
//		this.setWhereClause("WHERE EMPLOYEE=100466");
		//@ TODO DEBUG END   - remove lines
		pym = findPayMastr();
		while (hasMore()) {
			output.write(writePR560Layout(pym) + "\n");
			output.flush();
			pym = nextPayMastr();
		}
		//Update current date/time
		now = new Date();
		System.out.println(outputFile.getName() + " Finish " + df.format(now));
		//Write timestamp to output file
//		output.write(outputFile.getName() + " Finish " + df.format(now) + "\n");
		output.close();
	} catch (IOException e) {
		// Could not write properties file
		// Could not create output file
		System.out.println("IOException: " + e);
	}

  }
  
	//Output the PR560 format - Record Type 3
	public String writePR560Layout(TblPayMastr pym) {
		return (
				"3" + "," +						//RecType
				"1" + "," +
				pym.getEmployee() + "," +
				// @ TODO Add xlate when defined.
				xProcessLevels.xlate(pym.getProcess_level()) + "," +
//				pym.getProcess_level() + "," +
				// @ TODO Add xlate when defined.
//				xDepartments.xlate(pym.getDepartment()) + "," +		//Department
//				pym.getDepartment() + "," +							//Department
				"," +												//Department
				pym.getCheck_id() + "," +
				"," +
				"," +
				pym.getCheck_date() + "," +		//Tr_date
				pym.getCheck_date() + "," +		//Check_date
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				pym.getCheck_nbr() + "," +
				"," +
				"," +
				"," +
				pym.getCheck_net() + "," +
				pym.getEmpAchDist().getDist_amount() + "," +
				pym.getEmpAchDist().getBank_acct_nbr() + "," +
				pym.getEmpAchDist().getPartial_ach() + "," +
				pym.getEmpAchDist().getAch_bank() + "," +
				pym.getEmpAchDist().getAccount_type() + "," +
				pym.getEmpAchDist().getEbank_id() + "," +
				(pym.getEmpAchDist().getDist_amount()==0.0?"N":"Y") + "," +			//Partial_ACH - Y/N based on distribution amount
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				"," +
				","
				);
	}
}